package com.boxfishedu.foreignkey.entity.jpa;

import com.boxfishedu.foreignkey.entity.Student;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.transaction.annotation.Transactional;

import java.util.List;

/**
 * Created by lzx on 16/3/9.
 */
public interface StudentJpaRepository extends JpaRepository<Student,Long>,StudentJpaRepositoryCustom{

    /**
     * jpa映射衍生查询
     * @param key
     * @return
     */

    List<Student> findByUsernameContains(String key, Pageable pageable);

    /**
     * 使用jpql语句查询
     * @param key
     * @return
     */
    @Query(value = "SELECT s FROM Student s INNER JOIN s.subjects sub WHERE  s.username LIKE %?1% AND sub.name LIKE %?2%")
    List<Student> findByUsernameAndSubject(String key,String subjectName);

    /**
     * 原生sql语句查询
     * @param grade
     * @return
     */
    @Query(value = "SELECT username FROM student WHERE grade LIKE %?1%",nativeQuery = true)
    List<String> nativeFindByGrade(String grade);

    @Modifying
    @Transactional
    @Query(value = "UPDATE student SET username=?2 WHERE id=?1",nativeQuery = true)
    Student update(Long id,String username);

    Student findByUsername(String username);


}
